4  Subqueries

4.1 Reference

Default database in this chapter is “ap”.

Other databases included in the simulation MySQL server:

  • om
  • ex

4.2 Subqueries

What

Sometimes we want to express complex logic as part of the main query and wish that we could just write another standalone query for that logic.

Example: in WHERE or HAVING clause, we want to match/exclude only a specific set of values from another table that involves more complex subsetting in its own.

Why

  • More clear description of what complex logic taking place outside of main query.
  • More possibilities and flexibility to query data within one main query.

Example

The following subquery (getting vendor_id from vendors table where vendor_state is California) allows us subset invoices table based on vendor criteria in vendors table.

As you can imagine, if we need more complicated logic to subset for vendor_id, we can specify more in WHERE clause in the subquery. - much more expressive and telling how we select vendor_ids - more flexible if we need to add more logic/other operations in vendors table

SELECT invoice_number, invoice_date, invoice_total
FROM invoices
WHERE vendor_id IN
    (SELECT vendor_id
     FROM vendors
     WHERE vendor_state = 'CA')
ORDER BY invoice_date
LIMIT 15
Displaying records 1 - 15
invoice_number invoice_date invoice_total
125520-1 2022-04-24 95.00
97/488 2022-04-24 601.95
111-92R-10096 2022-04-30 16.33
25022117 2022-05-01 6.00
P02-88D77S7 2022-05-03 856.92
QP58872 2022-05-07 116.54
24863706 2022-05-10 6.00
10843 2022-05-11 4901.26
77290 2022-05-13 1750.00
121897 2022-05-19 450.00
P02-3772 2022-05-21 7125.34
97/486 2022-05-21 953.10
94007005 2022-05-23 220.00
RTR-72-3662-X 2022-05-25 1600.00
97/465 2022-05-25 565.15

4.3 Use Cases

Compare with an Aggregated Result

Here we want to retrieve all invoice records:

  • with balance due > 0
  • with balance_due below average of outstanding balance due

First, let’s see what average balance due looks like:

SELECT AVG(invoice_total - payment_total - credit_total)
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0
1 records
AVG(invoice_total - payment_total - credit_total)
2910.947

Second, let’s see what invoices balance due look like:

SELECT invoice_number, invoice_date, 
    invoice_total - payment_total - credit_total AS balance_due
FROM invoices
ORDER BY balance_due DESC
LIMIT 15
Displaying records 1 - 15
invoice_number invoice_date balance_due
P-0608 2022-07-23 19351.18
0-2436 2022-07-31 10976.06
31361833 2022-07-21 579.42
9982771 2022-07-24 503.20
547480102 2022-08-01 224.00
134116 2022-07-28 90.36
39104 2022-07-10 85.31
263253270 2022-07-22 67.92
263253268 2022-07-21 59.97
963253264 2022-07-18 52.25
263253273 2022-07-22 30.75
963253249 2022-08-02 0.00
963253251 2022-04-16 0.00
963253261 2022-04-16 0.00
I77271-O01 2022-04-26 0.00

Third, let’s see what invoices below average balance due look like:

SELECT invoice_number, invoice_date, 
    invoice_total - payment_total - credit_total AS balance_due
FROM invoices
WHERE invoice_total - payment_total - credit_total <
    (
     SELECT AVG(invoice_total - payment_total - credit_total)
     FROM invoices
     WHERE invoice_total - payment_total - credit_total > 0
    )
ORDER BY balance_due DESC
Displaying records 1 - 15
invoice_number invoice_date balance_due
31361833 2022-07-21 579.42
9982771 2022-07-24 503.20
547480102 2022-08-01 224.00
134116 2022-07-28 90.36
39104 2022-07-10 85.31
263253270 2022-07-22 67.92
263253268 2022-07-21 59.97
963253264 2022-07-18 52.25
263253273 2022-07-22 30.75
989319-457 2022-04-08 0.00
263253241 2022-04-10 0.00
963253234 2022-04-13 0.00
2-000-2993 2022-04-16 0.00
963253251 2022-04-16 0.00
963253261 2022-04-16 0.00

Lastly, filter out all of the invoices without any balance due:

SELECT invoice_number, invoice_date, 
    invoice_total - payment_total - credit_total AS balance_due
FROM invoices
WHERE invoice_total - payment_total - credit_total <
    (
     SELECT AVG(invoice_total - payment_total - credit_total)
     FROM invoices
     WHERE invoice_total - payment_total - credit_total > 0
    ) AND invoice_total - payment_total - credit_total > 0
ORDER BY balance_due DESC
9 records
invoice_number invoice_date balance_due
31361833 2022-07-21 579.42
9982771 2022-07-24 503.20
547480102 2022-08-01 224.00
134116 2022-07-28 90.36
39104 2022-07-10 85.31
263253270 2022-07-22 67.92
263253268 2022-07-21 59.97
963253264 2022-07-18 52.25
263253273 2022-07-22 30.75

Common Table Expressions (CTE)

Introduction

CTE allows you to assign nicknames for the output of your subqueries and then use the nicknames to go on to finish the main query.

Example, we want to write a query to:

  • get a vendor from each state
  • this vendor has the highest total invoice amount in that state
  • list all states with highest vendor invoice total in that state, and its vendor name

First, let’s get all vendors and their total invoices together:

SELECT vendor_state, 
    vendor_name,
    invoice_total
FROM vendors v INNER JOIN invoices i 
    ON v.vendor_id = i.vendor_id
Displaying records 1 - 15
vendor_state vendor_name invoice_total
NV United Parcel Service 3813.33
TN Federal Express Corporation 40.20
TN Federal Express Corporation 138.75
TN Federal Express Corporation 144.70
TN Federal Express Corporation 15.50
TN Federal Express Corporation 42.75
TN Federal Express Corporation 172.50
CA Evans Executone Inc 95.00
CA Zylka Design 601.95
TN Federal Express Corporation 42.67
TN Federal Express Corporation 42.50
AZ Wells Fargo Bank 662.00
CA Pacific Bell 16.33
CA Roadway Package System, Inc 6.00
CA Fresno County Tax Collector 856.92

Second, let’s get total invoice amount by vendors and states

SELECT vendor_state, 
    vendor_name,
    SUM(invoice_total) AS sum_of_invoices
FROM vendors v INNER JOIN invoices i 
    ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name
Displaying records 1 - 15
vendor_state vendor_name sum_of_invoices
NV United Parcel Service 23177.96
TN Federal Express Corporation 4378.02
CA Evans Executone Inc 95.00
CA Zylka Design 6940.25
AZ Wells Fargo Bank 662.00
CA Pacific Bell 171.01
CA Roadway Package System, Inc 43.67
CA Fresno County Tax Collector 856.92
OH Compuserve 19.90
CA IBM 1200.12
CA Yesmed, Inc 4901.26
CA Pollstar 1750.00
OH Edward Data Services 207.78
CA Gostanian General Building 450.00
CA Digital Dreamworks 7125.34

Third, now, we need to create a nickname for this result so that we can base on the result of that query to select the max total invoice amount by state:

WITH summary AS

(SELECT vendor_state, 
    vendor_name,
    SUM(invoice_total) AS sum_of_invoices
FROM vendors v INNER JOIN invoices i 
    ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name)

SELECT vendor_state,
    MAX(sum_of_invoices) AS sum_of_invoices
FROM summary
GROUP BY vendor_state 
10 records
vendor_state sum_of_invoices
NV 23177.96
TN 4378.02
CA 7125.34
AZ 662.00
OH 207.78
MI 119892.41
TX 2154.42
MA 1367.50
PA 265.36
DC 600.00

Lastly, we create nicknames for both results (yes, you can create CTE for any query that you wish to assign) and write the final query to get selected vendor names to each state and sum_of_invoices. (hnit: use inner join)

WITH summary AS
(
SELECT vendor_state, 
    vendor_name,
    SUM(invoice_total) AS sum_of_invoices
FROM vendors v INNER JOIN invoices i 
    ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name
),
top_in_state AS
(
SELECT vendor_state,
    MAX(sum_of_invoices) AS sum_of_invoices
FROM summary
GROUP BY vendor_state
)

SELECT summary.vendor_state, summary.vendor_name,
       top_in_state.sum_of_invoices
FROM summary INNER JOIN top_in_state
    ON summary.vendor_state = top_in_state.vendor_state AND
       summary.sum_of_invoices = top_in_state.sum_of_invoices
ORDER BY summary.vendor_state
10 records
vendor_state vendor_name sum_of_invoices
AZ Wells Fargo Bank 662.00
CA Digital Dreamworks 7125.34
DC Reiter’s Scientific & Pro Books 600.00
MA Dean Witter Reynolds 1367.50
MI Malloy Lithographing Inc 119892.41
NV United Parcel Service 23177.96
OH Edward Data Services 207.78
PA Cardinal Business Media, Inc. 265.36
TN Federal Express Corporation 4378.02
TX Ingram 2154.42

Recursive

Recursive querying can loop through a result set and perform processing to return a final result set. This is similar to “while loop” in other programming languages such as R, Python.

This is commonly used when you want to build a dataset reflecting on hierarchy: one parent record has many child records, each child record can have their own child records. For example, see the employees table where there are multiple managing levels embedded.

SELECT *
FROM ex.employees
9 records
employee_id last_name first_name department_number manager_id
1 Smith Cindy 2 NA
2 Jones Elmer 4 1
3 Simonian Ralph 2 2
4 Hernandez Olivia 1 9
5 Aaronsen Robert 2 4
6 Watson Denise 6 8
7 Hardy Thomas 5 2
8 O’Leary Rhea 4 9
9 Locario Paulo 6 1

Example: we will build an employee dataset with ranking order. Each employee has the same staff level will have the same ranking.

First, we start at the top of the hierachy.

SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL
1 records
employee_id employee_name ranking
1 Cindy Smith 1

Second, we experiment with the next level where staff are directly managed by employee_id 1. At this stage, they should have ranking increased by 1, as they are level 2 staff.

WITH employees_cte AS
(
SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL
)

SELECT employees.employee_id, 
    CONCAT(employees.first_name, ' ', employees.last_name) AS employee_name,
    1+1 AS ranking
FROM ex.employees INNER JOIN employees_cte ON employees.manager_id = employees_cte.employee_id
WHERE employees.employee_id = 2
1 records
employee_id employee_name ranking
2 Elmer Jones 2

Notice that if employees have the same level of manager at this point, it will all be returned with the same level of ranking. This is ensured by the “inner join” to employees table.

WITH employees_cte AS
(
SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL
)

SELECT employees.employee_id, 
    CONCAT(employees.first_name, ' ', employees.last_name) AS employee_name,
    1+1 AS ranking
FROM ex.employees INNER JOIN employees_cte ON employees.manager_id = employees_cte.employee_id
WHERE employees.employee_id = 9
1 records
employee_id employee_name ranking
9 Paulo Locario 2

If the employee doesn’t have the same management at this time in the employees_cte expression, the result will be empty.

WITH employees_cte AS
(
SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL
)

SELECT employees.employee_id, 
    CONCAT(employees.first_name, ' ', employees.last_name) AS employee_name,
    1+1 AS ranking
FROM ex.employees INNER JOIN employees_cte ON employees.manager_id = employees_cte.employee_id
WHERE employees.employee_id = 8
0 records
employee_id employee_name ranking

Third, imagine the following is happening:

  1. We are going through this exercise second time, add all of the second level of staff with ranking 2 and append to employees_cte;
  2. We are going through this exercise third time and add all of the third level of staff with ranking 3 and append to employees_cte;
  3. We are not stopping until we have every record remaining all return empty (i.e. nothing to further append to employees_cte).

Now, we have the following SQL statement to manifest that logic above.

With “UNION ALL”, we need to make sure your recursive query should have exactly the same columns and column names with the initial query.

WITH RECURSIVE employees_cte AS
(
-- Nonrecursive query
SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL

-- this to make sure the "append" action is taking place after each loop
UNION ALL 

-- Recursive query
SELECT employees.employee_id, 
    CONCAT(first_name, ' ', last_name), 
    ranking + 1
FROM ex.employees
    INNER JOIN employees_cte
    ON employees.manager_id = employees_cte.employee_id
)

SELECT *
FROM employees_cte
ORDER BY ranking, employee_id
9 records
employee_id employee_name ranking
1 Cindy Smith 1
2 Elmer Jones 2
9 Paulo Locario 2
3 Ralph Simonian 3
4 Olivia Hernandez 3
7 Thomas Hardy 3
8 Rhea O’Leary 3
5 Robert Aaronsen 4
6 Denise Watson 4

Use Subquery in FROM

Here, we want a query that:

  • Gets the names of all vendors.
  • Finds the latest invoice date for each vendor.
  • Lists the vendors with the most recent invoices at the top.

First, let’s see total invoice amounts by vendor name and invoice date

SELECT vendor_name, invoice_date
FROM invoices i LEFT JOIN vendors v ON i.vendor_id = v.vendor_id
Displaying records 1 - 15
vendor_name invoice_date
United Parcel Service 2022-04-08
Federal Express Corporation 2022-04-10
Federal Express Corporation 2022-04-13
Federal Express Corporation 2022-04-16
Federal Express Corporation 2022-04-16
Federal Express Corporation 2022-04-16
Federal Express Corporation 2022-04-21
Evans Executone Inc 2022-04-24
Zylka Design 2022-04-24
Federal Express Corporation 2022-04-24
Federal Express Corporation 2022-04-25
Wells Fargo Bank 2022-04-26
Pacific Bell 2022-04-30
Roadway Package System, Inc 2022-05-01
Fresno County Tax Collector 2022-05-03

Second, let’s see the latest invoice date by each vendor

SELECT vendor_name, MAX(invoice_date) AS latest_inv
FROM 
( SELECT vendor_name, invoice_date
  FROM invoices i LEFT JOIN vendors v ON i.vendor_id = v.vendor_id ) t
GROUP BY vendor_name
ORDER BY latest_inv DESC
Displaying records 1 - 15
vendor_name latest_inv
Federal Express Corporation 2022-08-02
Blue Cross 2022-08-01
Malloy Lithographing Inc 2022-07-31
Cardinal Business Media, Inc. 2022-07-28
Zylka Design 2022-07-25
United Parcel Service 2022-07-24
Ford Motor Credit Company 2022-07-24
Ingram 2022-07-21
Wakefield Co 2022-07-20
Reiter’s Scientific & Pro Books 2022-07-19
Pacific Bell 2022-07-15
Suburban Propane 2022-07-15
Data Reproductions Corp 2022-07-10
Abbey Office Furnishings 2022-07-05
Cahners Publishing Company 2022-06-30

Alternative: Use Subquery in SELECT

This is a second solution to the same problem statement:

“We want a query that:

  • Gets the names of all vendors.
  • Finds the latest invoice date for each vendor.
  • Lists the vendors with the most recent invoices at the top.”
SELECT vendor_name,
    (SELECT MAX(invoice_date) 
     FROM invoices
     WHERE vendor_id = vendors.vendor_id) AS latest_inv
FROM vendors
ORDER BY latest_inv DESC
Displaying records 1 - 15
vendor_name latest_inv
Federal Express Corporation 2022-08-02
Blue Cross 2022-08-01
Malloy Lithographing Inc 2022-07-31
Cardinal Business Media, Inc. 2022-07-28
Zylka Design 2022-07-25
Ford Motor Credit Company 2022-07-24
United Parcel Service 2022-07-24
Ingram 2022-07-21
Wakefield Co 2022-07-20
Reiter’s Scientific & Pro Books 2022-07-19
Pacific Bell 2022-07-15
Suburban Propane 2022-07-15
Data Reproductions Corp 2022-07-10
Abbey Office Furnishings 2022-07-05
Cahners Publishing Company 2022-06-30

Another Alternative: Use CTE

Now, we have learned common table expression and let’s see another implementation of this query. First, let’s see all invoices of their latest invoice date

SELECT vendor_id, MAX(invoice_date) 
FROM invoices
GROUP BY vendor_id
Displaying records 1 - 15
vendor_id MAX(invoice_date)
34 2022-06-09
37 2022-08-01
48 2022-05-03
72 2022-07-10
80 2022-07-28
81 2022-06-21
82 2022-07-19
83 2022-07-21
86 2022-06-11
88 2022-05-15
89 2022-04-24
90 2022-07-20
94 2022-07-05
95 2022-07-15
96 2022-04-26

Second, we can create a common table expression for the aggregated result above and use join mechanism to bring vendor name and other information from vendor table

WITH t AS 
(SELECT vendor_id, MAX(invoice_date) AS latest_inv
FROM invoices
GROUP BY vendor_id)

SELECT vendor_name, latest_inv 
FROM vendors LEFT JOIN t ON t.vendor_id = vendors.vendor_id
ORDER BY latest_inv DESC
Displaying records 1 - 15
vendor_name latest_inv
Federal Express Corporation 2022-08-02
Blue Cross 2022-08-01
Malloy Lithographing Inc 2022-07-31
Cardinal Business Media, Inc. 2022-07-28
Zylka Design 2022-07-25
Ford Motor Credit Company 2022-07-24
United Parcel Service 2022-07-24
Ingram 2022-07-21
Wakefield Co 2022-07-20
Reiter’s Scientific & Pro Books 2022-07-19
Pacific Bell 2022-07-15
Suburban Propane 2022-07-15
Data Reproductions Corp 2022-07-10
Abbey Office Furnishings 2022-07-05
Cahners Publishing Company 2022-06-30

4.4 Write Complex Queries

  1. State the problem to be solved by the query in plain language.
  2. Use pseudocode to outline the query.
  3. Code the subqueries and test them to be sure that they return the correct data.
  4. Code and test the final query.
SELECT t1.vendor_state, vendor_name, t1.sum_of_invoices
FROM
(
    -- sum of invoice totals by vendor
    SELECT vendor_state, vendor_name,
        SUM(invoice_total) AS sum_of_invoices
    FROM vendors v JOIN invoices i 
        ON v.vendor_id = i.vendor_id
    GROUP BY vendor_state, vendor_name
) t1

JOIN
(
  -- top sum of invoice totals by state
  SELECT vendor_state,  
         MAX(sum_of_invoices)
         AS sum_of_invoices
  FROM
  (
    -- sum of invoice totals by vendor
    SELECT vendor_state, vendor_name,
        SUM(invoice_total)
        AS sum_of_invoices
    FROM vendors v JOIN invoices i 
        ON v.vendor_id = i.vendor_id
    GROUP BY vendor_state, vendor_name
  ) t2
  GROUP BY vendor_state
) t3
    ON t1.vendor_state = t3.vendor_state AND 
       t1.sum_of_invoices = t3.sum_of_invoices
ORDER BY vendor_state
10 records
vendor_state vendor_name sum_of_invoices
AZ Wells Fargo Bank 662.00
CA Digital Dreamworks 7125.34
DC Reiter’s Scientific & Pro Books 600.00
MA Dean Witter Reynolds 1367.50
MI Malloy Lithographing Inc 119892.41
NV United Parcel Service 23177.96
OH Edward Data Services 207.78
PA Cardinal Business Media, Inc. 265.36
TN Federal Express Corporation 4378.02
TX Ingram 2154.42

4.5 Logical Operators

The best way to understand logical operators is to put them into use.

ALL

  • x > ALL (1, 2) <=> x > 2
  • x < ALL (1, 2) <=> x < 1
  • x = ALL (1, 2) <=> (x = 1) AND (x = 2)
  • x != ALL (1, 2)<=> x NOT IN (1, 2)

Example: we want to get all of the invoices with invoice_totals > the largest invoice_totals from vendor with vendor_id 34.

SELECT vendor_name, invoice_number, invoice_total
FROM invoices i JOIN vendors v ON i.vendor_id = v.vendor_id
WHERE invoice_total > ALL
    (SELECT invoice_total
     FROM invoices
     WHERE vendor_id = 34)
ORDER BY vendor_name
Displaying records 1 - 15
vendor_name invoice_number invoice_total
Bertelsmann Industry Svcs. Inc 509786 6940.25
Cahners Publishing Company 587056 2184.50
Computerworld 367447 2433.00
Data Reproductions Corp 40318 21842.00
Dean Witter Reynolds 75C-90227 1367.50
Digital Dreamworks P02-3772 7125.34
Franchise Tax Board RTR-72-3662-X 1600.00
Ingram 31359783 1575.00
Malloy Lithographing Inc 0-2058 37966.19
Malloy Lithographing Inc P-0259 26881.40
Malloy Lithographing Inc P-0608 20551.18
Malloy Lithographing Inc 0-2060 23517.58
Malloy Lithographing Inc 0-2436 10976.06
Pollstar 77290 1750.00
United Parcel Service 989319-457 3813.33

ANY

  • x > ANY (1, 2) <=> x > 1
  • x < ANY (1, 2) <=> x < 2
  • x = ANY (1, 2) <=> x IN (1, 2)
  • x != ANY (1, 2) <=> (x != 1) OR (x != 2)

Example: we want to get all of the invoices with invoice_totals < the largest invoice_totals from vendor with vendor_id 115.

SELECT vendor_name, invoice_number, invoice_total
FROM vendors JOIN invoices
  ON vendors.vendor_id = invoices.vendor_id
WHERE invoice_total < ANY
    (SELECT invoice_total
     FROM invoices
     WHERE vendor_id = 115)
Displaying records 1 - 15
vendor_name invoice_number invoice_total
Federal Express Corporation 963253251 15.50
Pacific Bell 111-92R-10096 16.33
Roadway Package System, Inc 25022117 6.00
Compuserve 21-4748363 9.95
Federal Express Corporation 4-321-2596 10.00
Roadway Package System, Inc 24863706 6.00
Compuserve 21-4923721 9.95
Federal Express Corporation 4-342-8069 10.00
Pacific Bell 111-92R-10094 19.67
Pacific Bell 111-92R-10097 16.33
Federal Express Corporation 4-314-3057 13.75
Federal Express Corporation 963253267 23.50
Roadway Package System, Inc 24780512 6.00
Federal Express Corporation 43966316 10.00
Abbey Office Furnishings 203339-13 17.50

IN

If a value is in a list of values.

Subquery should return a single column (i.e. a list of values).

Example: the following returns all vendor names and their state where they have invoice total less than $100.

SELECT vendor_name, vendor_state
FROM vendors
WHERE vendor_name IN 
  ( SELECT vendor_name
    FROM invoices i INNER JOIN vendors v ON i.vendor_id = v.vendor_id
    WHERE invoice_total < 100)
ORDER BY vendor_name
10 records
vendor_name vendor_state
Abbey Office Furnishings CA
Cardinal Business Media, Inc. PA
Coffee Break Service CA
Compuserve OH
Data Reproductions Corp MI
Evans Executone Inc CA
Federal Express Corporation TN
Pacific Bell CA
Roadway Package System, Inc CA
Suburban Propane CA

EXSITS

Example: The following gets all of vendors with existing invoices

SELECT vendor_id, vendor_name, vendor_state
FROM vendors
WHERE EXISTS
    (SELECT * 
     FROM invoices
     WHERE vendor_id = vendors.vendor_id)
Displaying records 1 - 15
vendor_id vendor_name vendor_state
34 IBM CA
37 Blue Cross CA
48 Fresno County Tax Collector CA
72 Data Reproductions Corp MI
80 Cardinal Business Media, Inc. PA
81 Wang Laboratories, Inc. CA
82 Reiter’s Scientific & Pro Books DC
83 Ingram TX
86 Computerworld CA
88 Edward Data Services OH
89 Evans Executone Inc CA
90 Wakefield Co CA
94 Abbey Office Furnishings CA
95 Pacific Bell CA
96 Wells Fargo Bank AZ

To negate, i.e. to find all vendors that don’t have any invoices yet. (“NOT EXISTS”)

SELECT vendor_id, vendor_name, vendor_state
FROM vendors
WHERE NOT EXISTS
    (SELECT * 
     FROM invoices
     WHERE vendor_id = vendors.vendor_id)
Displaying records 1 - 15
vendor_id vendor_name vendor_state
1 US Postal Service WI
2 National Information Data Ctr DC
3 Register of Copyrights DC
4 Jobtrak CA
5 Newbrige Book Clubs NJ
6 California Chamber Of Commerce CA
7 Towne Advertiser’s Mailing Svcs CA
8 BFI Industries CA
9 Pacific Gas & Electric CA
10 Robbins Mobile Lock And Key CA
11 Bill Marvin Electric Inc CA
12 City Of Fresno CA
13 Golden Eagle Insurance Co CA
14 Expedata Inc CA
15 ASC Signs CA